package org.codefinger.dao.impl.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import org.codefinger.dao.impl.DataAccesser;
import org.codefinger.dao.impl.QueryBuilder;
import org.codefinger.dao.impl.TableInfo;
import org.codefinger.dao.impl.TableInfo.TablePojo;
import org.codefinger.dao.impl.TableInfo.ValueGetter;
import org.codefinger.dao.impl.sql.UValueItem;
import org.codefinger.dao.util.Lang;
import org.codefinger.dao.util.jdbc.JDBCUtil;

public class OracleDataAccesser extends DataAccesser {

	private static final int[]	RETURN_KEY	= new int[] { 1 };

	public OracleDataAccesser(DataSource dataSource) {
		super(dataSource);
	}

	@Override
	protected QueryBuilder createQueryBuilder() {
		return new OracleQueryBuilder(this);
	}

	@Override
	protected TableInfo createTableInfo(String table) {
		table = table.toUpperCase();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();

			statement = connection.prepareStatement("SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ?");
			statement.setString(1, table);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				statement.close();
				resultSet.close();
			} else {
				throw Lang.makeThrow("Can not find the table named '%s'.", table);
			}

			statement = connection.prepareStatement("SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = ? AND CONSTRAINT_TYPE = 'P')");
			statement.setString(1, table);
			resultSet = statement.executeQuery();
			String primaryKeyName;
			if (resultSet.next()) {
				primaryKeyName = resultSet.getString(1);
				statement.close();
				resultSet.close();
			} else {
				throw Lang.makeThrow("Can not find the primary key from the table '%s'.", table);
			}

			String primaryKey = null;
			List<String> columns = new ArrayList<String>();

			statement = connection.prepareStatement("SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ?");
			statement.setString(1, table);
			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				String columnName = resultSet.getString(1);
				if (columnName.equals(primaryKeyName)) {
					primaryKey = columnName;
				}
				columns.add(columnName);
			}
			statement.close();
			resultSet.close();

			statement = connection.prepareStatement("SELECT COUNT(SEQUENCE_NAME) FROM ALL_SEQUENCES WHERE ALL_SEQUENCES.SEQUENCE_NAME = ?");
			String sequenceName = Lang.joinString("SEQ$", table);
			statement.setString(1, sequenceName);
			resultSet = statement.executeQuery();
			resultSet.next();

			if (resultSet.getInt(1) == 0) {
				statement.close();
				statement = connection.prepareStatement(Lang.joinString("CREATE SEQUENCE ", sequenceName, " START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 100"));
				statement.execute();
			}

			StringBuilder insertSQLBuilder = new StringBuilder("INSERT INTO ").append(table).append("(").append(primaryKey);
			for (String name : columns) {
				if (name.equals(primaryKey)) {
					continue;
				}
				insertSQLBuilder.append(',').append(name);
			}
			insertSQLBuilder.append(") VALUES (").append(sequenceName).append(".NEXTVAL");
			int size = columns.size();
			for (int i = 1; i < size; i++) {
				insertSQLBuilder.append(",?");
			}
			return new TableInfo(table, primaryKey, columns.toArray(new String[size]), insertSQLBuilder.append(')').toString());
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Matched an error during find infomation from the table '%s'.", table);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
	}

	@Override
	protected QueryBuilder createWhereBuilder() {
		return new OracleQueryBuilder();
	}

	@Override
	protected QueryBuilder createUValueBuilder() {
		return new OracleQueryBuilder(new ArrayList<UValueItem>());
	}

	@Override
	protected QueryBuilder createIValueBuilder(String table) {
		return new OracleQueryBuilder(this, table);
	}

	@Override
	public boolean insert(String table, Object pojo) {
		TableInfo tableInfo = getTableInfo(table);
		TablePojo<?> tablePojo = tableInfo.getTablePojo(pojo.getClass());
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(tableInfo.getInsertSQL(), RETURN_KEY);
			Iterator<ValueGetter> iterator = tablePojo.getValueGetters();
			int index = 1;
			while (iterator.hasNext()) {
				JDBCUtil.setStatementParam(statement, iterator.next().getValue(pojo), index++);
			}
			if (statement.executeUpdate() == 1) {
				resultSet = statement.getGeneratedKeys();
				resultSet.next();
				tablePojo.setKeyValue(pojo, resultSet);
				return true;
			}
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Insert into the table '%s' error.", table);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
		return false;
	}

	@Override
	public long executeInsert(String sql, Object[] params) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.prepareStatement(sql, RETURN_KEY);
			JDBCUtil.setStatementParams(statement, params);
			if (statement.executeUpdate() == 1) {
				resultSet = statement.getGeneratedKeys();
				if (resultSet.next()) {
					return resultSet.getLong(1);
				}
			}
			throw Lang.makeThrow("Execute sql error, '%s'.", sql);
		} catch (Throwable throwable) {
			throw Lang.wrapThrow(throwable, "Execute sql error, '%s'.", sql);
		} finally {
			JDBCUtil.freeResource(connection, statement, resultSet);
		}
	}
}
